import CityDataObtain
import DbServer
import hashMD5
from concurrent.futures import ThreadPoolExecutor
import threading

lock = threading.Lock()


# 爬取所有城市
def Run1():
    url_city = "https://www.jianzhimao.com/ctrlcity/changeCity.html"
    response = CityDataObtain.Response(url_city)
    list_tuple = list(CityDataObtain.GetCityData(response))
    sql = """
            create table if not exists `citys`(
            `city` varchar(30) not null,
            `id` varchar(255) not null,
            `url` varchar(255) not null,
            `status` int(1) not null default '0' comment '0未获取数据，1为已获取，-1为获取失败',
            primary key (`id`)
            ) engine=innodb default charset=utf8;
            """
    stu = DbServer.CreateTable(sql)
    if stu == 500:
        print("创建失败1")
        return -1
    for city, url in list_tuple:
        id = hashMD5.MD5(city+url)
        sql = "insert ignore into citys (city, id , url) values ('%s', '%s', '%s');" % (city, id, url)
        stu = DbServer.InsertData(sql)
        if stu == 400:
            print("添加失败")
            return -1
    print("ok1")


# 爬取所有城市所有区域的链接
def Run2():
    sql = """
            create table if not exists `regions`(
            `region` varchar(30) not null,
            `id` varchar(255) not null,
            `city_id` varchar(255) not null,
            `url` varchar(255) not null,
            `status` int(1) not null default '0' comment '0未获取数据，1为已获取，-1为获取失败',
            primary key (`id`)
            ) engine=innodb default charset=utf8;
            """
    stu = DbServer.CreateTable(sql)
    if stu == 500:
        print("创建失败2")
        return -1
    sql = "select city,id,url,status from citys;"
    datas = DbServer.ShowData(sql)
    if datas == 100:
        print("查询失败")
        return -1
    for city, city_id, city_url, city_status in datas:
        if city_status == 1 or city_status == -1:
            continue
        response = CityDataObtain.Response(city_url)
        if response.status_code != 200:
            sql = "update citys set status = -1 where id = '%s'" % (city_id)
            DbServer.UpdataData(sql)
            continue
        list_tuple = list(CityDataObtain.GetRegionData(city_url, response))
        if list_tuple == []:
            id = hashMD5.MD5(city + city_url)
            sql = "insert ignore into regions (region, id, city_id, url) values ('%s','%s','%s','%s');" % (city, id, city_id, city_url)
            stu = DbServer.InsertData(sql)
            sql = "update citys set status = 1 where id = '%s'" % (city_id)
            DbServer.UpdataData(sql)
            continue
        # print(list_tuple)
        for region, region_url in list_tuple:
            region_url = region_url.replace(" ", "")
            sql = "update citys set status = 1 where id = '%s'" % (city_id)
            DbServer.UpdataData(sql)
            id = hashMD5.MD5(region + region_url)
            sql = "insert ignore into regions (region, id, city_id, url) values ('%s','%s','%s','%s');" % (region, id, city_id, region_url)
            stu = DbServer.InsertData(sql)
            if stu == 400:
                print("添加失败")
                return -1
    print("ok2")


# 多线程入库兼职信息模块1
# 爬取所有区域兼职的标题，链接，浏览量，发布时间
def ThreadPool_1(region, region_id, region_url, region_status, city_url):
    global lock
    if region_status != 0:
        return 1
    for cont in range(1, 12):
        Purl = region_url + f"index{cont}.html"
        response = CityDataObtain.Response(Purl)
        if response.status_code != 200 and cont == 1:
            lock.acquire()
            sql = "update regions set status = -1 where id = '%s'" % (region_id)
            DbServer.UpdataData(sql)
            lock.release()
            break
        list_tuple = list(CityDataObtain.GetPageData(city_url, response))
        if list_tuple == [] and cont == 1:
            lock.acquire()
            sql = "update regions set status = -1 where id = '%s'" % (region_id)
            DbServer.UpdataData(sql)
            lock.release()
            break
        elif list_tuple == []:
            break

        print(region + f"第{cont}页")
        for title, url, visits, date in list_tuple:
            lock.acquire()
            id = hashMD5.MD5(title+url)
            sql = "insert ignore into jianzhi (title, region, id, region_id, url, visits, date) " \
                  "values ('%s','%s','%s','%s','%s',%d,'%s');" % (
            title, region, id, region_id, url, visits, date)
            stu = DbServer.InsertData(sql)
            if stu == 400:
                print("添加失败", sql)
                return 1
            print("添加成功", sql)
            sql = "update regions set status = 1 where id = '%s'" % (region_id)
            stu = DbServer.UpdataData(sql)
            lock.release()



# 多线程入库兼职信息模块2
# 爬取所有城市兼职的：
# 工作类型，招聘人数，上班地点，工作种类，工作时间，上班时段，结算方式，基本工资，工作详情，公司名称，公司介绍，公司地址
def ThreadPool_2(jianzhi_id, jianzhi_url, status):
    global lock
    if status != 0:
        return 1
    response = CityDataObtain.Response(jianzhi_url)
    list_tuple = list(CityDataObtain.GetJanZhi(response))
    for job_type, people, job_address, time_ask, job_kind, working_time, settlement, wages, job_detailed, \
                 company, company_introduction, company_address in list_tuple:
        lock.acquire()
        sql = """update jianzhi
                set job_type = '%s',people = %d,job_address = '%s',time_ask = '%s',job_kind = '%s',working_time = '%s',
                settlement = '%s',wages = '%s',job_detailed = '%s',company = '%s',company_introduction = '%s',company_address = '%s'
                where id = '%s'""" % (job_type, people, job_address, time_ask, job_kind, working_time, settlement,
                                      wages, job_detailed, company, company_introduction, company_address, jianzhi_id)
        stu = DbServer.UpdataData(sql)
        if stu == 200:
            print(jianzhi_id+"添加失败")
        else:
            sql = "update jianzhi set status = 1 where id = '%s'" % (jianzhi_id)
            stu = DbServer.UpdataData(sql)
        print("*")
        lock.release()


if __name__ == '__main__':
    Run1()
    Run2()

    # 线程模块1
    sql_1 = """
                create table if not exists `jianzhi`(
                `title` varchar(255) not null,
                `region` varchar(30) not null,
                `id` varchar(255) not null,
                `region_id` varchar(255) not null,
                `url` varchar(255) not null,
                `visits` int(255),
                `date` varchar(30),
                `job_type` varchar(10),
                `people` int(10),
                `job_address` varchar(100),
                `time_ask` varchar(100),
                `job_kind` varchar(30),
                `working_time` varchar(30),
                `settlement` varchar(30),
                `wages` int(255),
                `job_detailed` varchar(255),
                `company` varchar(255),
                `company_introduction` varchar(255),
                `company_address` varchar(255),
                `status` int(1) not null default '0' comment '0未获取数据，1为已获取，-1为获取失败或没有数据',
                primary key (`id`)
                ) engine=innodb default charset=utf8;
                """
    create_statu = DbServer.CreateTable(sql_1)
    if create_statu == 500:
        print("创建失败")
    sql_2 = """
            select r.region, r.id, r.url, r.status, c.url as c_url
            from  regions as r, citys as c
            where r.city_id = c.id
            """
    shows_1 = DbServer.ShowData(sql_2)
    with ThreadPoolExecutor(100) as t:
        for region, region_id, region_url, region_status, city_url in shows_1:
            t.submit(ThreadPool_1, region, region_id, region_url, region_status, city_url)

    # 线程模块2
    sql_3 = "select id,url,status from jianzhi"
    shows_2 = DbServer.ShowData(sql_3)
    with ThreadPoolExecutor(100) as t:
        for jianzhi_id, jianzhi_url, status in shows_2:
            t.submit(ThreadPool_2, jianzhi_id, jianzhi_url, status)



